1 Imports System.Text
2 Imports System.Security.Cryptography
3 Imports System.Data.OleDb
4
5 Public Class frmMain
6 Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|datadirectory|\hrs.mdb"
7 Dim connect As New OleDbConnection
8
9 #Region "Connections"
10 Public Sub openconnection()
11 If connect.State = ConnectionState.Closed Then
12 connect.ConnectionString = connstring
13 connect.Open()
14 ElseIf connect.State = ConnectionState.Open Then
15 Me.Refresh()
16 End If
17 End Sub
18
19 Public Sub closeconnection()
20 If connect.State = ConnectionState.Open Then
21 connect.Close()
22 ElseIf connect.State = ConnectionState.Closed Then
23 Me.Refresh()
24 End If
25 End Sub
26 #End Region
27
28 #Region "When form load"
29 Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
30 enablemenubtn()
31 hidegb()
32
33 dtpStartdate.Format = DateTimePickerFormat.Custom
34 dtpStartdate.CustomFormat = "dd/MM/yyyy"
35
36 dtpEnddate.Format = DateTimePickerFormat.Custom
37 dtpEnddate.CustomFormat = "dd/MM/yyyy"
38 End Sub
39 #End Region
40
41 #Region "Menu Buttons"
42
43 #Region "View all Customers"
44
45 #Region "Menu Button"
46 Private Sub btnMenuCustomers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMenuCustomers.Click
47 viewall()
48 enablemenubtn()
49 btnMenuCustomers.Enabled = False
50 hidegb()
51 gbCustomers.Show()
52 gbCustomers.Dock = DockStyle.Fill
53 End Sub
54 #End Region
55
56 #Region "Search Button"
57 Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
58 Dim search As String = txtSearch.Text.Trim
59 If (search = "" Or IsNothing(search) = True) Then
60 MsgBox("Please enter something to search", MessageBoxIcon.Warning, "Error")
61 txtSearch.Focus()
62 Exit Sub
63 Else
64 openconnection()
65 Dim searchqry As String = "SELECT id,fname,mname,lname,mstatus,houseno,roomno,amountpaid,amountremain,startdate,enddate FROM customers WHERE id LIKE '%" + search + "%' OR fname LIKE '%" + search + "%' OR mname LIKE '%" + search + "%' OR lname LIKE '%" + search + "%' OR mstatus LIKE '%" + search + "%' OR houseno LIKE '%" + search + "%' OR roomno LIKE '%" + search + "%' OR amountpaid LIKE '%" + search + "%' OR amountremain LIKE '%" + search + "%' OR startdate LIKE '%" + search + "%' OR enddate LIKE '%" + search + "%'"
66 Dim da As OleDbDataAdapter
67 Dim dtset As New DataSet
68 Dim dttable As New DataTable
69 da = New OleDbDataAdapter(searchqry, connect)
70 Try
71 da.Fill(dtset, "customers")
72 Catch ex As Exception
73 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
74 Exit Sub
75 End Try
76 dttable = dtset.Tables("customers")
77
78 dgvCustomers.DataSource = dttable
79 dgvCustomers.ColumnHeadersDefaultCellStyle.Font = New Font(dgvCustomers.Font, FontStyle.Bold)
80 dgvCustomers.Font = New Font(dgvCustomers.Font, FontStyle.Regular)
81
82 dgvCustomers.Columns("ID").HeaderText = "S/N"
83 dgvCustomers.Columns("ID").Width = 50
84
85 dgvCustomers.Columns("fname").HeaderText = "First Name"
86 dgvCustomers.Columns("fname").Width = 120
87
88 dgvCustomers.Columns("mname").HeaderText = "Middle Name"
89 dgvCustomers.Columns("mname").Width = 120
90
91 dgvCustomers.Columns("lname").HeaderText = "Last Name"
92 dgvCustomers.Columns("lname").Width = 120
93
94 dgvCustomers.Columns("mstatus").HeaderText = "Marital"
95 dgvCustomers.Columns("mstatus").Width = 80
96
97 dgvCustomers.Columns("houseno").HeaderText = "House No"
98 dgvCustomers.Columns("houseno").Width = 80
99
100 dgvCustomers.Columns("roomno").HeaderText = "Room"
101 dgvCustomers.Columns("roomno").Width = 80
102
103 dgvCustomers.Columns("amountpaid").HeaderText = "Amount Paid"
104 dgvCustomers.Columns("amountpaid").Width = 100
105
106 dgvCustomers.Columns("amountremain").HeaderText = "Amount Remain"
107 dgvCustomers.Columns("amountremain").Width = 100
108
109 dgvCustomers.Columns("startdate").HeaderText = "Start Date"
110 dgvCustomers.Columns("startdate").Width = 100
111
112 dgvCustomers.Columns("enddate").HeaderText = "End Date"
113 dgvCustomers.Columns("enddate").Width = 100
114
115 closeconnection()
116 txtSearch.Clear()
117 End If
118 End Sub
119 #End Region
120
121 #Region "Edit Button"
122 Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
123 If lblDeletereference.Text = "delete reference" Then
124 MsgBox("Please select a customer from the list", MessageBoxIcon.Warning, "Error")
125 Exit Sub
126
127 Else
128 openconnection()
129 Dim selectqry As String = "SELECT * FROM customers WHERE id=" + lblDeletereference.Text
130 Dim da As OleDbDataAdapter
131 da = New OleDbDataAdapter(selectqry, connect)
132 Dim dtset As DataSet
133 dtset = New DataSet
134 Try
135 da.Fill(dtset, "customers")
136 Catch ex As Exception
137 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
138 Exit Sub
139 End Try
140 Dim dttable As DataTable
141 dttable = New DataTable
142 dttable = dtset.Tables("customers")
143
144 Dim dbid, dbfname, dbmname, dblname, dbmstatus, dbjob, dbaddress, dbmobile, dbhouseno, dbroomno, dbamountpaid, dbamountremain, dbstartdate, dbenddate As String
145 For Each temprow In dttable.Rows
146 dbid = temprow("id").ToString
147 dbfname = temprow("fname").ToString
148 dbmname = temprow("mname").ToString
149 dblname = temprow("lname").ToString
150 dbmstatus = temprow("mstatus").ToString
151 dbjob = temprow("job").ToString
152 dbaddress = temprow("address").ToString
153 dbmobile = temprow("mobile").ToString
154 dbhouseno = temprow("houseno").ToString
155 dbroomno = temprow("roomno").ToString
156 dbamountpaid = temprow("amountpaid").ToString
157 dbamountremain = temprow("amountremain").ToString
158 dbstartdate = temprow("startdate").ToString
159 dbenddate = temprow("enddate").ToString
160 Next
161
162 lblDeletereference.Text = "delete reference"
163 hidegb()
164 hideerrors()
165 clearinputs()
166 gbAddCustomer.Show()
167 gbAddCustomer.Dock = DockStyle.Fill
168 enablemenubtn()
169 btnMenuAddCustomers.Enabled = False
170 lblEditreference.Text = dbid
171 txtFname.Text = dbfname
172 txtMname.Text = dbmname
173 txtLname.Text = dblname
174 cbMstatus.Text = dbmstatus
175 txtJob.Text = dbjob
176 txtAddress.Text = dbaddress
177 txtMobile.Text = dbmobile
178 txtHouseno.Text = dbhouseno
179 txtRoomno.Text = dbroomno
180 txtAmountpaid.Text = dbamountpaid
181 txtAmountremain.Text = dbamountremain
182 dtpStartdate.Text = dbstartdate
183 dtpEnddate.Text = dbenddate
184 End If
185 End Sub
186 #End Region
187
188 #Region "Delete Button"
189 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
190 If lblDeletereference.Text = "delete reference" Then
191 MsgBox("Please select a customer from the list", MessageBoxIcon.Warning, "Error")
192 Exit Sub
193
194 Else
195 Dim deleteqry As String = "DELETE FROM customers WHERE id=" + lblDeletereference.Text
196 Dim deletecmd As New OleDbCommand
197 Dim deletemsg As String = MessageBox.Show("Are you sure you want to delete user with ID " + lblDeletereference.Text + "?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
198
199 If deletemsg = MsgBoxResult.No Then
200 Me.Refresh()
201 viewall()
202 lblDeletereference.Text = "delete reference"
203 ElseIf deletemsg = MsgBoxResult.Yes Then
204 openconnection()
205 With deletecmd
206 .CommandText = deleteqry
207 .Connection = connect
208 Try
209 .ExecuteNonQuery()
210 MsgBox("Customer deleted successfully", MessageBoxIcon.Information, "Success")
211 viewall()
212 lblDeletereference.Text = "delete reference"
213 closeconnection()
214 Catch ex As Exception
215 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
216 closeconnection()
217 Exit Sub
218 End Try
219 End With
220 End If
221 End If
222 End Sub
223 #End Region
224
225 #Region "Refresh Button"
226 Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click
227 viewall()
228 txtSearch.Clear()
229 lblDeletereference.Text = "delete reference"
230 lblEditreference.Text = "edit reference"
231 End Sub
232 #End Region
233
234 #Region "All customers"
235 Private Sub viewall()
236 openconnection()
237
238 Dim selectqry As String = "SELECT id,fname,mname,lname,mstatus,houseno,roomno,amountpaid,amountremain,startdate,enddate FROM customers"
239 Dim da As OleDbDataAdapter
240 da = New OleDbDataAdapter(selectqry, connect)
241 Dim dtset As DataSet
242 dtset = New DataSet
243 Try
244 da.Fill(dtset, "customers")
245 Catch ex As Exception
246 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
247 Exit Sub
248 End Try
249 Dim dttable As DataTable
250 dttable = New DataTable
251 dttable = dtset.Tables("customers")
252
253 dgvCustomers.DataSource = dttable
254 dgvCustomers.ColumnHeadersDefaultCellStyle.Font = New Font(dgvCustomers.Font, FontStyle.Bold)
255 dgvCustomers.Font = New Font(dgvCustomers.Font, FontStyle.Regular)
256
257 dgvCustomers.Columns("ID").HeaderText = "S/N"
258 dgvCustomers.Columns("ID").Width = 50
259
260 dgvCustomers.Columns("fname").HeaderText = "First Name"
261 dgvCustomers.Columns("fname").Width = 120
262
263 dgvCustomers.Columns("mname").HeaderText = "Middle Name"
264 dgvCustomers.Columns("mname").Width = 120
265
266 dgvCustomers.Columns("lname").HeaderText = "Last Name"
267 dgvCustomers.Columns("lname").Width = 120
268
269 dgvCustomers.Columns("mstatus").HeaderText = "Marital"
270 dgvCustomers.Columns("mstatus").Width = 80
271
272 dgvCustomers.Columns("houseno").HeaderText = "House No"
273 dgvCustomers.Columns("houseno").Width = 80
274
275 dgvCustomers.Columns("roomno").HeaderText = "Room"
276 dgvCustomers.Columns("roomno").Width = 80
277
278 dgvCustomers.Columns("amountpaid").HeaderText = "Amount Paid"
279 dgvCustomers.Columns("amountpaid").Width = 100
280
281 dgvCustomers.Columns("amountremain").HeaderText = "Amount Remain"
282 dgvCustomers.Columns("amountremain").Width = 100
283
284 dgvCustomers.Columns("startdate").HeaderText = "Start Date"
285 dgvCustomers.Columns("startdate").Width = 100
286
287 dgvCustomers.Columns("enddate").HeaderText = "End Date"
288 dgvCustomers.Columns("enddate").Width = 100
289
290 closeconnection()
291 End Sub
292 #End Region
293
294 #Region "When click customer"
295 Private Sub dgvCustomers_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvCustomers.CellContentClick
296 Dim s As System.Drawing.Point
297 s = dgvCustomers.CurrentCellAddress
298 Dim id As String = dgvCustomers.Item("ID", s.Y).Value.ToString
299 lblDeletereference.Text = id
300 End Sub
301 #End Region
302
303 #End Region
304
305 #Region "Add new Customers"
306
307 #Region "Menu Button"
308 Private Sub btnMenuAddCustomers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMenuAddCustomers.Click
309 enablemenubtn()
310 btnMenuAddCustomers.Enabled = False
311 hidegb()
312 gbAddCustomer.Show()
313 gbAddCustomer.Dock = DockStyle.Fill
314 hideerrors()
315 clearinputs()
316 End Sub
317 #End Region
318
319 #Region "Confirm Button"
320 Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
321 Dim fname As String = txtFname.Text.Trim
322 Dim mname As String = txtMname.Text.Trim
323 Dim lname As String = txtLname.Text.Trim
324 Dim mstatus As String = cbMstatus.Text
325 Dim job As String = txtJob.Text.Trim
326 Dim address As String = txtAddress.Text.Trim
327 Dim mobile As String = txtMobile.Text.Trim
328 Dim houseno As String = txtHouseno.Text.Trim
329 Dim roomno As String = txtRoomno.Text.Trim
330 Dim amountpaid As String = txtAmountpaid.Text.Trim
331 Dim amountremain As String = txtAmountremain.Text.Trim
332 Dim startdate As String = dtpStartdate.Text
333 Dim enddate As String = dtpEnddate.Text
334
335 If fname = "" Or IsNothing(fname) = True Then
336 hideerrors()
337 lblErrorfname.Show()
338 lblErrorfname.Text = "Please enter first name"
339 txtFname.Focus()
340 Exit Sub
341 ElseIf fname.Length < 3 Then
342 hideerrors()
343 lblErrorfname.Show()
344 lblErrorfname.Text = "First name must be greater than 3 characters"
345 txtFname.Focus()
346 Exit Sub
347 ElseIf txtFname.Text.Contains(" ") = True Then
348 hideerrors()
349 lblErrorfname.Show()
350 lblErrorfname.Text = "Please enter first name correctly"
351 txtFname.Focus()
352 Exit Sub
353 ElseIf txtFname.Text.Length > 24 Then
354 hideerrors()
355 lblErrorfname.Show()
356 lblErrorfname.Text = "First name must be less than 25 characters"
357 txtFname.Focus()
358 Exit Sub
359
360 ElseIf mname = "" Or IsNothing(mname) = True Then
361 hideerrors()
362 lblErrormname.Show()
363 lblErrormname.Text = "Please enter middle name"
364 txtMname.Focus()
365 Exit Sub
366 ElseIf mname.Length < 3 Then
367 hideerrors()
368 lblErrormname.Show()
369 lblErrormname.Text = "Middle name must be greater than 3 characters"
370 txtMname.Focus()
371 Exit Sub
372 ElseIf txtMname.Text.Contains(" ") = True Then
373 hideerrors()
374 lblErrormname.Show()
375 lblErrormname.Text = "Please enter middle name correctly"
376 txtMname.Focus()
377 Exit Sub
378 ElseIf txtMname.Text.Length > 24 Then
379 hideerrors()
380 lblErrormname.Show()
381 lblErrormname.Text = "Middle name must be less than 25 characters"
382 txtMname.Focus()
383 Exit Sub
384
385 ElseIf lname = "" Or IsNothing(lname) = True Then
386 hideerrors()
387 lblErrorlname.Show()
388 lblErrorlname.Text = "Please enter last name"
389 txtLname.Focus()
390 Exit Sub
391 ElseIf lname.Length < 3 Then
392 hideerrors()
393 lblErrorlname.Show()
394 lblErrorlname.Text = "Last name must be greater than 3 characters"
395 txtLname.Focus()
396 Exit Sub
397 ElseIf txtLname.Text.Contains(" ") = True Then
398 hideerrors()
399 lblErrorlname.Show()
400 lblErrorlname.Text = "Please enter last name correctly"
401 txtLname.Focus()
402 Exit Sub
403 ElseIf txtLname.Text.Length > 24 Then
404 hideerrors()
405 lblErrorlname.Show()
406 lblErrorlname.Text = "Last name must be less than 25 characters"
407 txtLname.Focus()
408 Exit Sub
409
410 ElseIf mstatus = "" Or IsNothing(mname) = True Then
411 hideerrors()
412 lblErrormstatus.Show()
413 lblErrormstatus.Text = "Please select marital status"
414 cbMstatus.Focus()
415 Exit Sub
416
417 ElseIf job = "" Or IsNothing(job) = True Then
418 hideerrors()
419 lblErrorjob.Show()
420 lblErrorjob.Text = "Please enter job"
421 txtJob.Focus()
422 Exit Sub
423 ElseIf job.Length < 5 Then
424 hideerrors()
425 lblErrorjob.Show()
426 lblErrorjob.Text = "Job must be greater than 5 characters"
427 txtJob.Focus()
428 Exit Sub
429 ElseIf txtJob.Text.Length > 55 Then
430 hideerrors()
431 lblErrorjob.Show()
432 lblErrorjob.Text = "Job must be less than 55 characters"
433 txtJob.Focus()
434 Exit Sub
435
436 ElseIf address = "" Or IsNothing(address) = True Then
437 hideerrors()
438 lblErroraddress.Show()
439 lblErroraddress.Text = "Please enter address"
440 txtAddress.Focus()
441 Exit Sub
442 ElseIf address.Length < 5 Then
443 hideerrors()
444 lblErroraddress.Show()
445 lblErroraddress.Text = "Address must be greater than 5 characters"
446 txtAddress.Focus()
447 Exit Sub
448 ElseIf txtAddress.Text.Length > 49 Then
449 hideerrors()
450 lblErroraddress.Show()
451 lblErroraddress.Text = "Address must be less than 50 characters"
452 txtAddress.Focus()
453 Exit Sub
454
455 ElseIf mobile = "" Or IsNothing(mobile) = True Then
456 hideerrors()
457 lblErrormobile.Show()
458 lblErrormobile.Text = "Please enter mobile number"
459 txtMobile.Focus()
460 Exit Sub
461 ElseIf txtMobile.Text.Contains(" ") = True Then
462 hideerrors()
463 lblErrormobile.Show()
464 lblErrormobile.Text = "Please enter mobile number correctly"
465 txtMobile.Focus()
466 Exit Sub
467 ElseIf IsNumeric(txtMobile.Text.Trim) = False Then
468 hideerrors()
469 lblErrormobile.Show()
470 lblErrormobile.Text = "Please enter numbers only"
471 txtMobile.Focus()
472 Exit Sub
473 ElseIf txtMobile.Text.Length > 14 Then
474 hideerrors()
475 lblErrormobile.Show()
476 lblErrormobile.Text = "Mobile number must be less than 15 numbers"
477 txtMobile.Focus()
478 Exit Sub
479
480 ElseIf houseno = "" Or IsNothing(houseno) = True Then
481 hideerrors()
482 lblErrorhouseno.Show()
483 lblErrorhouseno.Text = "Please enter house number"
484 txtHouseno.Focus()
485 Exit Sub
486 ElseIf houseno.Length > 10 Then
487 hideerrors()
488 lblErrorhouseno.Show()
489 lblErrorhouseno.Text = "House number must me less than 10 characters"
490 txtHouseno.Focus()
491 Exit Sub
492 ElseIf txtHouseno.Text.StartsWith("house ") = False Then
493 hideerrors()
494 lblErrorhouseno.Show()
495 lblErrorhouseno.Text = "Error. Please refer to example above"
496 txtHouseno.Focus()
497 Exit Sub
498
499 ElseIf roomno = "" Or IsNothing(roomno) = True Then
500 hideerrors()
501 lblErrorroomno.Show()
502 lblErrorroomno.Text = "Please enter room number"
503 txtRoomno.Focus()
504 Exit Sub
505 ElseIf roomno.Length > 5 Then
506 hideerrors()
507 lblErrorroomno.Show()
508 lblErrorroomno.Text = "Room number must be less than 5 digits"
509 txtRoomno.Focus()
510 Exit Sub
511 ElseIf txtRoomno.Text.Contains(" ") = True Then
512 hideerrors()
513 lblErrorroomno.Show()
514 lblErrorroomno.Text = "Please enter room number correctly"
515 txtRoomno.Focus()
516 Exit Sub
517 ElseIf IsNumeric(txtRoomno.Text.Trim) = False Then
518 hideerrors()
519 lblErrorroomno.Show()
520 lblErrorroomno.Text = "Please enter numbers only"
521 txtRoomno.Focus()
522 Exit Sub
523
524 ElseIf amountpaid = "" Or IsNothing(amountpaid) = True Then
525 hideerrors()
526 lblErroramountpaid.Show()
527 lblErroramountpaid.Text = "Please enter amount paid"
528 txtAmountpaid.Focus()
529 Exit Sub
530 ElseIf txtAmountpaid.Text.Contains(" ") = True Then
531 hideerrors()
532 lblErroramountpaid.Show()
533 lblErroramountpaid.Text = "Please enter amount paid correctly"
534 txtAmountpaid.Focus()
535 Exit Sub
536 ElseIf IsNumeric(txtAmountpaid.Text.Trim) = False Then
537 hideerrors()
538 lblErroramountpaid.Show()
539 lblErroramountpaid.Text = "Please enter numbers only"
540 txtAmountpaid.Focus()
541 Exit Sub
542 ElseIf txtAmountpaid.Text.Length > 19 Then
543 hideerrors()
544 lblErroramountpaid.Show()
545 lblErroramountpaid.Text = "Amount paid must be less than 20 digits"
546 txtAmountpaid.Focus()
547 Exit Sub
548
549 ElseIf amountremain = "" Or IsNothing(amountremain) = True Then
550 hideerrors()
551 lblErroramountremain.Show()
552 lblErroramountremain.Text = "Please enter amount remain"
553 txtAmountremain.Focus()
554 Exit Sub
555 ElseIf txtAmountremain.Text.Contains(" ") = True Then
556 hideerrors()
557 lblErroramountremain.Show()
558 lblErroramountremain.Text = "Please enter amount remained correctly"
559 txtAmountremain.Focus()
560 Exit Sub
561 ElseIf IsNumeric(txtAmountremain.Text.Trim) = False Then
562 hideerrors()
563 lblErroramountremain.Show()
564 lblErroramountremain.Text = "Please enter numbers only"
565 txtAmountremain.Focus()
566 Exit Sub
567 ElseIf txtAmountremain.Text.Length > 19 Then
568 hideerrors()
569 lblErroramountremain.Show()
570 lblErroramountremain.Text = "Amount remain must be less than 20 digits"
571 txtAmountremain.Focus()
572 Exit Sub
573
574 ElseIf lblEditreference.Text <> "edit reference" Then
575 openconnection()
576 Dim selectqry As String = "SELECT id FROM customers WHERE id=" + lblEditreference.Text
577 Dim da As OleDbDataAdapter
578 da = New OleDbDataAdapter(selectqry, connect)
579 Dim dtset As DataSet
580 dtset = New DataSet
581 Try
582 da.Fill(dtset, "customers")
583 Catch ex As Exception
584 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
585 Exit Sub
586 End Try
587
588 Dim dttable As DataTable
589 dttable = New DataTable
590 dttable = dtset.Tables("customers")
591
592 Dim dbid As String
593 For Each temprow In dttable.Rows
594 dbid = temprow("id").ToString
595
596 Dim ask As String = "User exist. Do you want to update his/her informations?"
597 Dim mymsgbox As String = MessageBox.Show(ask, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
598
599 If mymsgbox = MsgBoxResult.No Then
600 Me.Refresh()
601 clearinputs()
602 lblEditreference.Text = "edit reference"
603 closeconnection()
604 btnConfirm.Enabled = True
605 Exit Sub
606 Else
607 Dim updateqry As String = "UPDATE customers SET fname='" + fname + "',mname='" + mname + "',lname='" + lname + "',mstatus='" + mstatus + "',job='" + job + "',address='" + address + "',mobile='" + mobile + "',houseno='" + houseno + "',roomno='" + roomno + "',amountpaid='" + amountpaid + "',amountremain='" + amountremain + "',startdate='" + startdate + "',enddate='" + enddate + "' WHERE id=" + lblEditreference.Text
608 Dim updatecmd As New OleDbCommand
609 With updatecmd
610 .CommandText = updateqry
611 .Connection = connect
612
613 Try
614 .ExecuteNonQuery()
615 clearinputs()
616 MsgBox("Customer details updated successfuly", MsgBoxStyle.Information, "Success")
617 lblEditreference.Text = "edit reference"
618 closeconnection()
619 btnConfirm.Enabled = True
620 Exit Sub
621 Catch ex As Exception
622 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
623 hideerrors()
624 Exit Sub
625 End Try
626 End With
627 End If
628 Next
629
630 ElseIf lblEditreference.Text = "edit reference" Then
631 hideerrors()
632 openconnection()
633 Dim insertqry As String = "INSERT INTO customers(fname,mname,lname,mstatus,job,address,mobile,houseno,roomno,amountpaid,amountremain,startdate,enddate) VALUES('" + fname + "','" + mname + "','" + lname + "','" + mstatus + "','" + job + "','" + address + "','" + mobile + "','" + houseno + "','" + roomno + "','" + amountpaid + "','" + amountremain + "','" + startdate + "','" + enddate + "')"
634 Dim insertcmd As New OleDbCommand
635 With insertcmd
636 .CommandText = insertqry
637 .Connection = connect
638
639 Try
640 .ExecuteNonQuery()
641 clearinputs()
642 MsgBox("Customer added successfuly", MsgBoxStyle.Information, "Success")
643 btnConfirm.Enabled = True
644 Exit Sub
645 Catch ex As Exception
646 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
647 hideerrors()
648 Exit Sub
649 End Try
650 End With
651 End If
652 End Sub
653 #End Region
654
655 #Region "Clear Button"
656 Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
657 clearinputs()
658 End Sub
659 #End Region
660
661 #Region "Hide all error labels"
662 Private Sub hideerrors()
663 lblErrorfname.Hide()
664 lblErrormname.Hide()
665 lblErrorlname.Hide()
666 lblErrormstatus.Hide()
667 lblErrorjob.Hide()
668 lblErroraddress.Hide()
669 lblErrormobile.Hide()
670 lblErrorhouseno.Hide()
671 lblErrorroomno.Hide()
672 lblErroramountpaid.Hide()
673 lblErroramountremain.Hide()
674 lblErrorstartdate.Hide()
675 lblErrorenddate.Hide()
676 End Sub
677 #End Region
678
679 #Region "Clear inputs"
680 Private Sub clearinputs()
681 txtFname.Clear()
682 txtMname.Clear()
683 txtLname.Clear()
684 txtJob.Clear()
685 txtAddress.Clear()
686 txtMobile.Clear()
687 txtHouseno.Clear()
688 txtRoomno.Clear()
689 txtAmountpaid.Clear()
690 txtAmountremain.Clear()
691 hideerrors()
692 End Sub
693 #End Region
694
695 #Region "Textbox fname changed"
696 Private Sub txtFname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFname.TextChanged
697 If txtFname.Text.Length > 24 Then
698 hideerrors()
699 lblErrorfname.Show()
700 lblErrorfname.Text = "First name must be less than 25 characters"
701 txtFname.Focus()
702 btnConfirm.Enabled = False
703 Exit Sub
704 Else
705 hideerrors()
706 btnConfirm.Enabled = True
707 Exit Sub
708 End If
709 End Sub
710 #End Region
711
712 #Region "Textbox mname changed"
713 Private Sub txtMname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMname.TextChanged
714 If txtMname.Text.Length > 24 Then
715 hideerrors()
716 lblErrormname.Show()
717 lblErrormname.Text = "Middle name must be less than 25 characters"
718 txtMname.Focus()
719 btnConfirm.Enabled = False
720 Exit Sub
721 Else
722 hideerrors()
723 btnConfirm.Enabled = True
724 Exit Sub
725 End If
726 End Sub
727 #End Region
728
729 #Region "Textbox lname changed"
730 Private Sub txtLname_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtLname.TextChanged
731 If txtLname.Text.Length > 24 Then
732 hideerrors()
733 lblErrorlname.Show()
734 lblErrorlname.Text = "Last name must be less than 25 characters"
735 txtLname.Focus()
736 btnConfirm.Enabled = False
737 Exit Sub
738 Else
739 hideerrors()
740 btnConfirm.Enabled = True
741 Exit Sub
742 End If
743 End Sub
744 #End Region
745
746 #Region "Textbox job changed"
747 Private Sub txtJob_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtJob.TextChanged
748 If txtJob.Text.Length > 55 Then
749 hideerrors()
750 lblErrorjob.Show()
751 lblErrorjob.Text = "Job must be less than 55 characters"
752 txtJob.Focus()
753 btnConfirm.Enabled = False
754 Exit Sub
755 Else
756 hideerrors()
757 btnConfirm.Enabled = True
758 End If
759 End Sub
760 #End Region
761
762 #Region "Textbox address changed"
763 Private Sub txtAddress_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAddress.TextChanged
764 If txtAddress.Text.Length > 49 Then
765 hideerrors()
766 lblErroraddress.Show()
767 lblErroraddress.Text = "Address must be less than 50 characters"
768 txtAddress.Focus()
769 btnConfirm.Enabled = False
770 Exit Sub
771 Else
772 hideerrors()
773 btnConfirm.Enabled = True
774 End If
775 End Sub
776 #End Region
777
778 #Region "Textbox mobile changed"
779 Private Sub txtMobile_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMobile.TextChanged
780 If IsNumeric(txtMobile.Text.Trim) = False Then
781 hideerrors()
782 lblErrormobile.Show()
783 lblErrormobile.Text = "Please enter numbers only"
784 txtMobile.Focus()
785 btnConfirm.Enabled = False
786 Exit Sub
787 ElseIf txtMobile.Text.Length > 14 Then
788 hideerrors()
789 lblErrormobile.Show()
790 lblErrormobile.Text = "Mobile number must be less than 15 numbers"
791 txtMobile.Focus()
792 btnConfirm.Enabled = False
793 Exit Sub
794 Else
795 hideerrors()
796 btnConfirm.Enabled = True
797 End If
798 End Sub
799 #End Region
800
801 #Region "Textbox houseno changed"
802 Private Sub txtHouseno_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtHouseno.TextChanged
803
804 If txtHouseno.Text.StartsWith("house ") = False Then
805 hideerrors()
806 lblErrorhouseno.Show()
807 lblErrorhouseno.Text = "Error. Please refer to example above"
808 txtHouseno.Focus()
809 btnConfirm.Enabled = False
810 Exit Sub
811 ElseIf txtHouseno.Text.Length > 9 Then
812 hideerrors()
813 lblErrorhouseno.Show()
814 lblErrorhouseno.Text = "House number must be less than 10 characters"
815 txtHouseno.Focus()
816 btnConfirm.Enabled = False
817 Exit Sub
818 Else
819 hideerrors()
820 btnConfirm.Enabled = True
821 End If
822 End Sub
823 #End Region
824
825 #Region "Textbox roomno changed"
826 Private Sub txtRoomno_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtRoomno.TextChanged
827 If IsNumeric(txtRoomno.Text.Trim) = False Then
828 hideerrors()
829 lblErrorroomno.Show()
830 lblErrorroomno.Text = "Please enter numbers only"
831 txtRoomno.Focus()
832 btnConfirm.Enabled = False
833 Exit Sub
834 ElseIf txtRoomno.Text.Length > 4 Then
835 hideerrors()
836 lblErrorroomno.Show()
837 lblErrorroomno.Text = "Room number must be less than 5 digits"
838 txtRoomno.Focus()
839 btnConfirm.Enabled = False
840 Exit Sub
841 Else
842 hideerrors()
843 btnConfirm.Enabled = True
844 End If
845 End Sub
846 #End Region
847
848 #Region "Textbox amountpaid changed"
849 Private Sub txtAmountpaid_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAmountpaid.TextChanged
850 If IsNumeric(txtAmountpaid.Text.Trim) = False Then
851 hideerrors()
852 lblErroramountpaid.Show()
853 lblErroramountpaid.Text = "Please enter numbers only"
854 txtAmountpaid.Focus()
855 btnConfirm.Enabled = False
856 Exit Sub
857 ElseIf txtAmountpaid.Text.Length > 19 Then
858 hideerrors()
859 lblErroramountpaid.Show()
860 lblErroramountpaid.Text = "Amount paid must be less than 20 digits"
861 txtAmountpaid.Focus()
862 btnConfirm.Enabled = False
863 Exit Sub
864 Else
865 hideerrors()
866 btnConfirm.Enabled = True
867 End If
868 End Sub
869 #End Region
870
871 #Region "Textbox amountremain changed"
872 Private Sub txtAmountremain_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtAmountremain.TextChanged
873 If IsNumeric(txtAmountremain.Text.Trim) = False Then
874 hideerrors()
875 lblErroramountremain.Show()
876 lblErroramountremain.Text = "Please enter numbers only"
877 txtAmountremain.Focus()
878 btnConfirm.Enabled = False
879 Exit Sub
880 ElseIf txtAmountremain.Text.Length > 19 Then
881 hideerrors()
882 lblErroramountremain.Show()
883 lblErroramountremain.Text = "Amount remain must be less than 20 digits"
884 txtAmountremain.Focus()
885 btnConfirm.Enabled = False
886 Exit Sub
887 Else
888 hideerrors()
889 btnConfirm.Enabled = True
890 End If
891 End Sub
892 #End Region
893 #End Region
894
895 #Region "Report"
896
897 #Region "Menu button"
898 Private Sub btnMenuReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMenuReport.Click
899 enablemenubtn()
900 btnMenuReport.Enabled = False
901 hidegb()
902 gbReport.Show()
903 gbReport.Dock = DockStyle.Fill
904 hidereporterrors()
905 End Sub
906 #End Region
907
908 #Region "Report by house number"
909 Private Sub btnReporthouse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReporthouse.Click
910 If txtReporthouse.Text = "" Or IsNothing(txtReporthouse.Text) = True Then
911 hidereporterrors()
912 lblErrorreporthouse.Show()
913 lblErrorreporthouse.Text = "Please enter house number"
914 txtReporthouse.Focus()
915 Exit Sub
916 ElseIf txtReporthouse.Text.StartsWith("house ") = False Then
917 hidereporterrors()
918 lblErrorreporthouse.Show()
919 lblErrorreporthouse.Text = "Error. Please refer to example above"
920 txtReporthouse.Focus()
921 Exit Sub
922 Else
923 With frmReportbyhouse
924 .myfilter = txtReporthouse.Text
925 End With
926 hidereporterrors()
927 Me.Hide()
928 txtReporthouse.Clear()
929 frmReportbyhouse.ShowDialog()
930 End If
931 End Sub
932 #End Region
933
934 #Region "Report by marital"
935 Private Sub btnReportMstatus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReportMstatus.Click
936 If cbReportMstatus.Text = "" Or IsNothing(cbReportMstatus.Text) = True Then
937 hidereporterrors()
938 lblErrorreportmstatus.Show()
939 lblErrorreportmstatus.Text = "Please select marital"
940 cbReportMstatus.Focus()
941 Exit Sub
942 Else
943 With frmReportbymarital
944 .myfilter = cbReportMstatus.Text
945 End With
946 hidereporterrors()
947 Me.Hide()
948 frmReportbymarital.ShowDialog()
949 End If
950 End Sub
951 #End Region
952
953 #Region "Report by standard"
954 Private Sub btnReportstandard_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReportstandard.Click
955 Me.Hide()
956 hideerrors()
957 frmReportbystandard.Show()
958 End Sub
959 #End Region
960
961 #Region "Hide report errors"
962 Private Sub hidereporterrors()
963 lblErrorreporthouse.Hide()
964 lblErrorreportmstatus.Hide()
965 End Sub
966 #End Region
967
968 #End Region
969
970 #Region "Change Password"
971
972 #Region "Menu Button"
973 Private Sub btnMenuPassword_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMenuPassword.Click
974 enablemenubtn()
975 btnMenuPassword.Enabled = False
976 hidegb()
977 gbPassword.Show()
978 gbPassword.Dock = DockStyle.Fill
979 hideerrors()
980 clearpassinputs()
981 End Sub
982 #End Region
983
984 #Region "Confirm Button"
985 Private Sub btnConfirmp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirmp.Click
986 Dim oldp As String = txtOldp.Text.Trim
987 Dim newp As String = txtNewp.Text.Trim
988 Dim confirmp As String = txtConfirmp.Text.Trim
989
990 If oldp = "" Or IsNothing(oldp) = True Then
991 hidepasserrors()
992 lblErrorOldp.Show()
993 lblErrorOldp.Text = "Please enter your old password"
994 txtOldp.Focus()
995 Exit Sub
996 ElseIf oldp.Length < 6 Then
997 hidepasserrors()
998 lblErrorOldp.Show()
999 lblErrorOldp.Text = "Password cannot have less than 6 characters"
1000 txtOldp.Focus()
1001 Exit Sub
1002
1003 ElseIf newp = "" Or IsNothing(newp) = True Then
1004 hidepasserrors()
1005 lblErrorNewp.Show()
1006 lblErrorNewp.Text = "Please enter your new password"
1007 txtNewp.Focus()
1008 Exit Sub
1009 ElseIf newp.Length < 6 Then
1010 hidepasserrors()
1011 lblErrorNewp.Show()
1012 lblErrorNewp.Text = "Password cannot have less than 6 characters"
1013 txtNewp.Focus()
1014 Exit Sub
1015
1016 ElseIf confirmp = "" Or IsNothing(confirmp) = True Then
1017 hidepasserrors()
1018 lblErrorConfirmp.Show()
1019 lblErrorConfirmp.Text = "Please confirm your new password"
1020 txtConfirmp.Focus()
1021 Exit Sub
1022 ElseIf confirmp.Length < 6 Then
1023 hidepasserrors()
1024 lblErrorConfirmp.Show()
1025 lblErrorConfirmp.Text = "Password cannot have less than 6 characters"
1026 txtConfirmp.Focus()
1027 Exit Sub
1028
1029 Else
1030 openconnection()
1031 Dim Ueold As New UnicodeEncoding()
1032 Dim ByteSourceTextold() As Byte = Ueold.GetBytes(oldp)
1033 Dim Md5old As New MD5CryptoServiceProvider()
1034 Dim ByteHashold() As Byte = Md5old.ComputeHash(ByteSourceTextold)
1035 Convert.ToBase64String(ByteHashold)
1036 Dim hasholdPwd As String
1037 hasholdPwd = Convert.ToBase64String(ByteHashold)
1038
1039 Dim Uenew As New UnicodeEncoding()
1040 Dim ByteSourceTextnew() As Byte = Uenew.GetBytes(newp)
1041 Dim Md5new As New MD5CryptoServiceProvider()
1042 Dim ByteHashnew() As Byte = Md5new.ComputeHash(ByteSourceTextnew)
1043 Convert.ToBase64String(ByteHashnew)
1044 Dim hashnewPwd As String
1045 hashnewPwd = Convert.ToBase64String(ByteHashnew)
1046
1047 Dim updateqry As String = "UPDATE users SET passwd='" + hashnewPwd + "' WHERE uname='cngirwa'"
1048 Dim cmd As OleDbCommand
1049 cmd = New OleDbCommand(updateqry, connect)
1050
1051 Dim selectqry As String = "SELECT uname,passwd FROM users WHERE uname='cngirwa'"
1052 Dim da As OleDbDataAdapter
1053 da = New OleDbDataAdapter(selectqry, connect)
1054 Dim dtset As DataSet
1055 dtset = New DataSet()
1056 Try
1057 da.Fill(dtset, "users")
1058 Catch ex As Exception
1059 MsgBox("Server is busy. Please try again", MessageBoxIcon.Error, "Error")
1060 Exit Sub
1061 End Try
1062
1063 Dim dttable As DataTable
1064 dttable = New DataTable()
1065 dttable = dtset.Tables("users")
1066
1067 Dim dboldp As String
1068 For Each tempRow In dttable.Rows
1069 dboldp = tempRow("passwd").ToString
1070
1071 If (oldp.Length > 5) And (newp.Length > 5) And (confirmp.Length > 5) Then
1072 If (dboldp <> hasholdPwd) Then
1073 MsgBox("Old password did not match", MessageBoxIcon.Warning, "Error")
1074 Exit Sub
1075
1076 ElseIf (dboldp = hashnewPwd) Then
1077 MsgBox("New password is the same as old password. Please enter new password", MessageBoxIcon.Warning, "Error")
1078 Exit Sub
1079
1080 ElseIf (dboldp = hasholdPwd) Then
1081 If (newp = confirmp) Then
1082 Try
1083 cmd.ExecuteNonQuery()
1084 MsgBox("Password changed successful", MsgBoxStyle.Information, "Success")
1085 clearpassinputs()
1086 closeconnection()
1087 Catch ex As Exception
1088 MsgBox("Error occured. Please try again", MessageBoxIcon.Error, "Error")
1089 Exit Sub
1090 End Try
1091 Else
1092 MsgBox("New password did not match", MessageBoxIcon.Warning, "Error")
1093 Exit Sub
1094 End If
1095 End If
1096 Else
1097 MsgBox("Password cannot have less than 6 characters", MessageBoxIcon.Warning, "Error")
1098 Exit Sub
1099 End If
1100 Next
1101 closeconnection()
1102 End If
1103 End Sub
1104 #End Region
1105
1106 #Region "Clear Button"
1107 Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
1108 clearpassinputs()
1109 End Sub
1110 #End Region
1111
1112 #Region "Clear inputs"
1113 Private Sub clearpassinputs()
1114 txtOldp.Clear()
1115 txtNewp.Clear()
1116 txtConfirmp.Clear()
1117 End Sub
1118 #End Region
1119
1120 #Region "Hide pass errors"
1121 Private Sub hidepasserrors()
1122 lblErrorOldp.Hide()
1123 lblErrorNewp.Hide()
1124 lblErrorConfirmp.Hide()
1125 End Sub
1126 #End Region
1127
1128 #End Region
1129
1130 #Region "About"
1131 Private Sub btnAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAbout.Click
1132 Dim apptitle As String = My.Application.Info.Title.ToString
1133 Dim compname As String = My.Application.Info.CompanyName.ToString
1134 Dim appversion As String = My.Application.Info.Version.ToString
1135 Dim appdescrption As String = My.Application.Info.Description.ToString
1136 Dim appcopyright As String = My.Application.Info.Copyright.ToString
1137
1138 MsgBox("Name : " & apptitle & ControlChars.NewLine & _
1139 "Company Name : " & compname & ControlChars.NewLine & _
1140 "Version : v" & appversion & ControlChars.NewLine & _
1141 "Description : " & appdescrption & ControlChars.NewLine & _
1142 appcopyright & ControlChars.NewLine & _
1143 "Credits : Amani Allen Moova (System Analyst) ")
1144 enablemenubtn()
1145 End Sub
1146 #End Region
1147
1148 #Region "Logout"
1149 Private Sub btnMenuLogout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMenuLogout.Click
1150 Dim close As String = MessageBox.Show("Are you sure you want to logout?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2)
1151 If close = MsgBoxResult.No Then
1152 Me.Refresh()
1153 enablemenubtn()
1154 hidegb()
1155 closeconnection()
1156 Else
1157 closeconnection()
1158 frmLogin.Show()
1159 Me.Dispose()
1160 Me.Close()
1161 End If
1162 End Sub
1163 #End Region
1164
1165 #End Region
1166
1167 #Region "Hide all group boxes"
1168 Private Sub hidegb()
1169 gbCustomers.Hide()
1170 gbAddCustomer.Hide()
1171 gbPassword.Hide()
1172 gbReport.Hide()
1173 End Sub
1174 #End Region
1175
1176 #Region "Enable menu buttons"
1177 Private Sub enablemenubtn()
1178 btnMenuCustomers.Enabled = True
1179 btnMenuAddCustomers.Enabled = True
1180 btnMenuReport.Enabled = True
1181 btnMenuPassword.Enabled = True
1182 btnMenuLogout.Enabled = True
1183 End Sub
1184 #End Region
1185
1186 End Class
